package com.vito.util.excel;

import com.vito.framework.base.exception.CommonException;
import com.vito.util.DataValidateUtil;
import jxl.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ImportExcelUtilImpl extends DataValidateUtil implements ImportExcelUtil {

	/**
	 * 解析导入的excel文件，返回数据集
	 * @param FileInputStream fis Excel文件流
	 * @param int sheetNum 导入工作薄sheet号，默认为0
	 * @param int beginRow 导入数据开始行行号，从0开始，默认为1
	 * @param int beginCol 导入数据开始列，从0开始，默认为0
	 * @param int colspan 导入列表共有多少列
	 * @return List<String[]>
	 * @throws CommonException
	 */
	@Override
	public List<String[]> readXLSDocument(InputStream fis, int sheetNum, int beginRow, int beginCol, int colspan)
			throws CommonException {
		//从输入流读取EXCEL文件
		Workbook workBook = null;
		List<String[]> list = null;
		try {
			workBook = Workbook.getWorkbook(fis);
			//获取EXCEL文件工作薄内容，从sheet0开始
			Sheet sheet = workBook.getSheet(sheetNum);
			list = readXLSDocumentBySheet(sheet,beginRow,beginCol,colspan);
		} catch (CommonException e) {
			throw e;
		} catch (Exception e) {
			throw new CommonException("读取文件失败");
		}finally{
			try {
				if(fis!=null){fis.close();}
			} catch (IOException e) {
				throw new CommonException("关闭文件流失败");
			}
		}
		return list;
	}

	/**
	 * 解析导入的excel文件，返回数据集
	 * @param FileInputStream fis Excel文件流，默认读取第一个sheet数据
	 * @param int beginRow 导入数据开始行行号，第0行为表头，默认从1开始读取数据
	 * @param int colspan 导入列表共有多少列,默认从第一列开始导入
	 * @return List<String[]>
	 * @throws CommonException
	 */
	@Override
	public List<String[]> readXLSDocument(InputStream fis, int beginRow,
	                                      int colspan) throws CommonException {
		//从输入流读取EXCEL文件
		Workbook workBook = null;
		List<String[]> list = null;
		try {
			workBook = Workbook.getWorkbook(fis);
			//获取EXCEL文件工作薄内容，从sheet0开始
			Sheet sheet = workBook.getSheet(0);
			list = readXLSDocumentBySheet(sheet,beginRow,0,colspan);
		} catch (CommonException e) {
			throw e;
		} catch (Exception e) {
			throw new CommonException("读取文件失败");
		}finally{
			try {
				if(fis!=null){fis.close();}
			} catch (IOException e) {
				throw new CommonException("关闭文件流失败");
			}
		}
		return list;
	}

	/**
	 * 从sheet中读取数据，返回数据集
	 * @param Sheet sheet 导入工作薄sheet
	 * @param int beginRow 导入数据开始行行号，从0开始，默认为1
	 * @param int beginCol 导入数据开始列，从0开始，默认为0
	 * @param int colspan 导入列表共有多少列
	 * @return List<String[]>
	 * @throws CommonException
	 */
	private List<String[]> readXLSDocumentBySheet(Sheet sheet, int beginRow, int beginCol, int colspan)throws CommonException {
		int rows = sheet.getRows(); // 得到表里的行数
		if(rows< beginRow+1){
			throw new CommonException("没有可导入的数据");
		}
		List<String[]> dataSet = new ArrayList<String[]>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String[] data = null;
		Cell cell = null;
		String content = null;
		for (int i = beginRow; i < rows; i++) {
			data = new String[colspan];
			for(int j=beginCol;j<colspan+beginCol;j++){
				cell = sheet.getCell(j,i);
				if(cell.getType() == CellType.DATE){
					content = sdf.format(((DateCell)cell).getDate());
				}else{
					content = cell.getContents();
				}
				data[j]=content;
			}
			dataSet.add(data);
		}
		return dataSet;
	}

	@Override
	public List<String[]> readXLSDocument(File file, int sheetNum,
	                                      int beginRow, int beginCol, int colspan) throws CommonException {
		//从输入流读取EXCEL文件
		Workbook workBook = null;
		List<String[]> list = null;
		FileInputStream fis = null;
		try {
			if(file == null){
				throw new CommonException("导入文件为空");
			}
			fis = new FileInputStream(file);
			workBook = Workbook.getWorkbook(fis);
			//获取EXCEL文件工作薄内容，从sheet0开始
			Sheet sheet = workBook.getSheet(sheetNum);
			list = readXLSDocumentBySheet(sheet,beginRow,beginCol,colspan);
		} catch (CommonException e) {
			throw e;
		} catch (Exception e) {
			throw new CommonException("读取文件失败");
		}finally{
			try {
				if(fis!=null){fis.close();}
			} catch (IOException e) {
				throw new CommonException("关闭文件流失败");
			}
		}
		return list;
	}

	@Override
	public List<String[]> readXLSDocument(File file, int beginRow, int colspan)
			throws CommonException {
		//从输入流读取EXCEL文件
		Workbook workBook = null;
		List<String[]> list = null;
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(file);
			workBook = Workbook.getWorkbook(fis);
			//获取EXCEL文件工作薄内容，从sheet0开始
			Sheet sheet = workBook.getSheet(0);
			list = readXLSDocumentBySheet(sheet,beginRow,0,colspan);
		} catch (CommonException e) {
			throw e;
		} catch (Exception e) {
			throw new CommonException("读取文件失败");
		}finally{
			try {
				if(fis!=null){fis.close();}
			} catch (IOException e) {
				throw new CommonException("关闭文件流失败");
			}
		}
		return list;
	}

}
